﻿if exists (select * from dbo.sysobjects where id = 
object_id(N'[dbo].[sp_SalesTotalsByAmountReport]') 
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_SalesTotalsByAmountReport]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE Procedure [sp_SalesTotalsByAmountReport]

As

Set NoCount On

SELECT 
  [OrderSubTotals].Subtotal AS SaleAmount, 
  [Order].ID, 
  Customer.Name, 
  [Order].ShippedDate,
  CAST(FLOOR([OrderSubTotals].Subtotal/1000) as int) AS GroupValue
FROM Customer INNER JOIN [Order]
   ON Customer.ID = [Order].CustomerID
inner join
(
SELECT 
  [OrderDetail].OrderID, 
  Sum(Cast([UnitPrice]*[Quantity]*(1-[Discount])/100  as smallmoney)*100) AS Subtotal
FROM [OrderDetail]
GROUP BY [OrderDetail].OrderID
) OrderSubTotals on [Order].ID = OrderSubTotals.OrderId 
WHERE ([OrderSubtotals].Subtotal >2500) AND 
      ([Order].ShippedDate Between '1997-01-01' And '1997-12-31')
order by [OrderSubTotals].Subtotal desc
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO